package com.bo.Dao.Impl;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.bo.Dao.CompanyIntDao;
import com.bo.Entity.CompanyInt;
import com.bo.Entity.MyImage;
import com.bo.util.MyLobStreamingResultSetExtractor;


@Repository
public class CompanyIntDaoImpl implements CompanyIntDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private LobHandler lobHandler;
	
	
	

	public List<CompanyInt> getCompany(int companyId) {

		String sql="select * from company_int where company_id=?";
		
		RowMapper<CompanyInt> mapper = new RowMapper<CompanyInt>() {
			public CompanyInt mapRow(ResultSet rs, int rowNum) throws SQLException {

				CompanyInt companyInt=new CompanyInt();
				companyInt.setId(rs.getInt("id"));
				companyInt.setModel(rs.getInt("model"));
				companyInt.setTitle(rs.getString("title"));
				companyInt.setContent(rs.getString("content"));
				companyInt.setCompanyId(rs.getInt("company_id"));
				
				companyInt.setImage1(new MyImage(1,rs.getString("image_name1")));
				companyInt.setImage2(new MyImage(2,rs.getString("image_name2")));
				
                return companyInt;
			}
		};
		
	    return jdbcTemplate.query(sql, new Object[]{companyId}, mapper);
	     
	}

	

	public void save ( final CompanyInt companyInt ) throws DataAccessException{

		jdbcTemplate.execute (
	       "INSERT INTO company_int (model,title,content,company_id,image_name1,image1,image_name2,image2) VALUES (?,?,?,?,?,?,?,?)",
				new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
					protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
						ps.setInt(1, companyInt.getModel());
						ps.setString(2, companyInt.getTitle());
						ps.setString(3, companyInt.getContent());
						ps.setInt(4, companyInt.getCompanyId());
						
						MyImage image1=companyInt.getImage1();
						ps.setString(5,image1.getImageName());
						lobCreator.setBlobAsBinaryStream(ps, 6, image1.getInputStream(), (int)image1.getSize());
						
						MyImage image2=companyInt.getImage2();
						ps.setString(7,image2.getImageName());
						lobCreator.setBlobAsBinaryStream(ps, 8, image2.getInputStream(), (int)image2.getSize());
					}
		});
	}
	
	

	public int update ( final CompanyInt companyInt ) throws DataAccessException{
		
		String sql="UPDATE company_int SET model=?,title=?,content=?,image_name1=?,image1=?, image_name2=?,image2=? WHERE id= ?";
		
		MyImage image1=companyInt.getImage1();
		MyImage image2=companyInt.getImage2();
		
		SqlLobValue lobValue1=new SqlLobValue(image1.getInputStream(), (int) image1.getSize(), lobHandler);
		SqlLobValue lobValue2=new SqlLobValue(image2.getInputStream(), (int) image2.getSize(), lobHandler);
      
		return 
		jdbcTemplate.update(sql ,new Object[] {companyInt.getModel(),companyInt.getTitle(),companyInt.getContent()
		  ,image1.getImageName(),lobValue1 ,image2.getImageName() ,lobValue2, companyInt.getId()}
		  ,new int[]{Types.INTEGER,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.BLOB,Types.VARCHAR,Types.BLOB,Types.INTEGER});
		
	}
	
	
	

	public int delete (int id){
		
		String sql="DELETE FROM company_int WHERE id=?";
		
		return jdbcTemplate.update(sql, id);
		
	}
	
	

	
	public void getImage(final int id,final MyImage image){
		
		String sql="SELECT image"+image.getId()+" FROM company_int WHERE id=?";
		
		jdbcTemplate.query(sql, new Object[] {id}
                           ,new MyLobStreamingResultSetExtractor(lobHandler, id, image));
	}


}
